Lookerの「行数表示制限5000件」の壁を超える

Lookerの「行数表示制限5000件」の壁を超える

先ずはSQLより始めよ
Clock Icon2023.10.23

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

まず最初に一番大事なことをお伝えしておくと、この記事のタイトルは凄く盛っています。昇天ペガサスくらい盛ってます。残念ながら壁を超えることはできません。

その代わりといっては何ですが、Looker5000件問題に対する対策方法をご紹介します。

「Looker5000件問題」とは

上記を読んでもらうのが一番間違いないのですが、簡単に説明すると、Lookerでは、何らかのデータを可視化するとき、一気に表示できる行数(データポイント)は5000件が限界となっています。5000件以上を表示することができません。

Looker上で、取得結果が5000件を超える可視化をしようとした場合、以下のようなアラートが表示されます。

こちら、Lookerというサービスの性能がどうのこうのというより、「表示するWebブラウザ側のこと」を考えた末の仕様と思われます。というのも、Webブラウザでストレスなく表示できる限界が5000件くらいだからです。仮に、Google Cloud本社に行って「頼むから5000件の限界を解除してください!!」と土下座を行い、限界突破してもらったとしても、5000件以上を一気に可視化しようとした際、Webブラウザ自体が固まるor落ちる可能性が高いと思います。なので、この限界を超えようとすること自体、ナンセンスなのです。

解決方法はPagenation(ページング)

5000件が表示の限界である以上、ここは発想を変えて「全データを5000件ずつ順繰りに表示していく」という方向で行きます。

んまあ、ちょっと検索すると、公式に説明している記事が出てくるんですけどね…。

分かる人はこの記事を読めばスッと終わります。が、この記事では、上記の方法をもう少しじっくり説明していきたいと思います。

SQLから考える

LookMLで何かを解決しようとするとき、まずは「最終的にLookerに実行させるSQLから考える」のが良いとされています(すみません、筆者が勝手に思っているだけです)。

SnowflakeやGoogle BigQueryなど、ほとんどのDWH(DB)には、SQLで使える機能としてLIMITOFFSETというものがあります。これを使うと、取得するデータの範囲を指定することができます。

BigQueryでちょこっと検証してみる

BigQueryのサンプルデータに対して、LIMITを使用してみます。LIMIT 20としているので、(どれだけデータがあろうとも)このクエリで表示するのは20件までとなります。

OFFSETを使用すると「表示を開始する件数の位置」を指定することができます。以下では、LIMIT 10に加えて、OFFSET 10としています。これは「実際に取得できる件数の11件目から、10件だけ表示する」という指定になります。

1枚目のスクショと比べるとわかりやすいと思います。IDの昇順に並べた結果の11件目(display_nameがGeoff Dalgas)から10件だけが表示されています。

LIMITとOFFSETを駆使すればページングができる

LIMITとOFFSETを使うことで、実際の取得結果から、好きな部分だけを表示できることがわかりました。

ここまでくれば、勘のいい人は分かると思います。LIMITとOFFSETの値を(Exploreから)動的に変更できるようにすれば、Looker上で5000件ずつページングできるのです!

実際にやってみた

上記のCommunityにある記事のコードを参考(というかほとんどそのまま)にし、以下のViewを作成しました。

view: users_page_nation {

  derived_table: {
    sql: select *
           from `bigquery-public-data.stackoverflow.users`
          limit {% parameter count_number %}
         offset {{ count_number._parameter_value | times: start_number._parameter_value | minus: count_number._parameter_value }}
    ;;
  }

  parameter: count_number {
    label: "取得件数"
    type: number
  }

  parameter: start_number {
    label: "開始ページ"
    type: number
  }
  
  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }
  
  dimension: display_name {
    type: string
    sql: ${TABLE}.display_name ;;
  }
  
  measure: count {
    type: count
  }

}

ポイントは2つのparameter(「開始ページ」と「取得件数」)です。このparameterに数値(ページングを開始したいページ目と取得する件数)を入れることで、任意のページング処理が実行された派生テーブルができます。

offsetの部分のLiquidについて

上記の派生テーブルにはparameterが組み込まれています。limit {% parameter count_number %}は、parameterに指定した値がそのまま代入されるだけなので、わかりやすいと思います。

しかし、offset {{ count_number._parameter_value | times: start_number._parameter_value | minus: count_number._parameter_value }}については、パッと見ただけだと何のこっちゃ?という感じですね。

これを式として表現すると以下のようになります。

「取得件数(count_number)」 × 「開始ページ目(start_number)」 - 「取得件数(count_number)」

例えば「5000件ずつ表示するとして、それの3ページ目を取得したい」とします。その場合、LIMITは5000ですが、OFFSETは「5000件ずつ表示した時の3ページ目の最初」となります。その場合、3ページ分の件数から1ページ分を引く…「5000×3-5000 」…つまり10000を指定する必要があります。このシンプルな掛け算をLiquidで実装すると、上記のコードになります。

LiquidにはFiltersという機能があります。これは渡した値(パラメータ)に対して、色々な処理をかけることができるというものです。|をつなげることで、複数の処理を連続で実行することもできます(左から右にいくイメージ)。

今回のoffsetのところでは、timesminusという2つの処理が記述されています。意味としてはそのまんま「乗算」と「減算」です。まず、渡された「取得件数(count_number)」に対して「開始ページ目(start_number)」を掛けて、その結果から「取得件数(count_number)」を引いています。結果的に、前述した式と同じ内容になっていると思います。

Explore上で生成されたSQLを確認した結果がこちら。ちゃんとなってますね。

Exploreで結果を確認してみる

先程の、BigQuery上で実行した「実際に取得できる件数の11件目から、10件だけ表示する」を、Looker上でやってみます。使用するのはもちろん前述したViewのusers_page_nation

2つのparameterをフィルタに設定して、開始ページの「2」と取得件数の「10」をセットしたところ…

見事に成功しました。

おわりに

ECサイトの売上分析などでは、5000件以上を一気に可視化することは少ないですが(年間の売上を日別に出したとしても365件なので)、センサーなどのIoT関連のデータとかだと、5000以上のデータを確認しないといけないケースは珍しくありません。そういう場合に、今回のテクを使ってみてください。

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.